--Query to find Requsition number by Providing autocreated PO number
select segment1 from po_requisition_headers_all where requisition_header_id in
(select requisition_header_id from po_requisition_lines_all where requisition_line_id in 
(select requisition_line_id from po_req_distributions_all where distribution_id in
(select req_distribution_id from po_distributions_all where po_header_id = 
(select po_Header_id from po_headers_all where segment1 = '<PO number>'))));
                                  
--Query to find autocreated PO number by providing Requsition number                                   
select segment1 from po_headers_all where po_header_id in(
select po_header_id from po_distributions_all where req_distribution_id in(
select distribution_id from po_req_distributions_all where requisition_line_id in(
select requisition_line_id from po_requisition_lines_all where requisition_header_id in(
select requisition_header_id from po_requisition_headers_all where segment1='<Req number>'))));

--Query to find Encumbrance journal details by providing PO number
Select jel.*, je_category "Header_Category", je_source "Header_Source", actual_flag "Header Flag", encumbrance_type
  from gl_je_lines jel, gl_je_headers jeh, gl_encumbrance_types jee
 where jel.je_header_id = jeh.je_header_id
   and jee.encumbrance_type_id(+) = jeh.encumbrance_type_id
   and jel.reference_2 in (select to_char(po_header_id)
                             from po_headers_all where segment1 = '68130');
-----Relation with Requistion and PO
select r.segment1 "Req Num",
       p.segment1 "PO Num"
from po_headers_all p, 
po_distributions_all d,
po_req_distributions_all rd, 
po_requisition_lines_all rl,
po_requisition_headers_all r 
where p.po_header_id = d.po_header_id 
and d.req_distribution_id = rd.distribution_id 
and rd.requisition_line_id = rl.requisition_line_id 
and rl.requisition_header_id = r.requisition_header_id 
                                  


--Display PO Approval groups
select  pcg.control_group_name
       ,pcg.description
       ,pcr.object_code
       ,pcr.rule_type_code
       ,pcr.amount_limit
       ,decode(pcr.object_code,'ACCOUNT_RANGE', pcr.segment1_low || '-' || pcr.segment2_low || '-' || pcr.segment3_low || '-' || pcr.segment4_low || '-' || pcr.segment5_low || '-' || pcr.segment6_low)
       ,decode(pcr.object_code,'ACCOUNT_RANGE', pcr.segment1_high || '-' || pcr.segment2_high || '-' || pcr.segment3_high || '-' || pcr.segment4_high || '-' || pcr.segment5_high || '-' || pcr.segment6_high)
from    PO_CONTROL_GROUPS_ALL pcg 
       ,PO_CONTROL_RULES pcr
where   pcg.org_id = 22970
and     pcg.control_group_id = pcr.control_group_id;


---used to list all Internal Requisitions that do not have an  associated Internal Sales order
Select RQH.SEGMENT1 REQ_NUM,
RQL.LINE_NUM,
RQL.REQUISITION_HEADER_ID ,
RQL.REQUISITION_LINE_ID,
RQL.ITEM_ID ,
RQL.UNIT_MEAS_LOOKUP_CODE ,
RQL.UNIT_PRICE ,
RQL.QUANTITY ,
RQL.QUANTITY_CANCELLED,
RQL.QUANTITY_DELIVERED ,
RQL.CANCEL_FLAG ,
RQL.SOURCE_TYPE_CODE ,
RQL.SOURCE_ORGANIZATION_ID ,
RQL.DESTINATION_ORGANIZATION_ID,
RQH.TRANSFERRED_TO_OE_FLAG
from
PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH
where
RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID
and RQL.SOURCE_TYPE_CODE = 'INVENTORY'
and RQL.SOURCE_ORGANIZATION_ID is not null
and not exists (select 'existing internal order'
from OE_ORDER_LINES_ALL LIN
where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID
and LIN.SOURCE_DOCUMENT_TYPE_ID = 10)
ORDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;


----- List all open PO'S
select 
h.segment1 "PO NUM", 
h.authorization_status "STATUS", 
l.line_num "SEQ NUM", 
ll.line_location_id, 
d.po_distribution_id , 
h.type_lookup_code "TYPE" 
from 
po.po_headers_all h, 
po.po_lines_all l, 
po.po_line_locations_all ll, 
po.po_distributions_all d 
where h.po_header_id = l.po_header_id 
and ll.po_line_id = l.po_Line_id 
and ll.line_location_id = d.line_location_id 
and h.closed_date is null 
and h.type_lookup_code not in ('QUOTATION') 


----- List and PO With there approval , invoice and payment details
select 
a.org_id "ORG ID", 
E.SEGMENT1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER(e.vendor_type_lookup_code) "VENDOR TYPE", 
f.vendor_site_code "VENDOR SITE CODE",
f.ADDRESS_LINE1 "ADDRESS",
f.city "CITY",
f.country "COUNTRY", 
to_char(trunc(d.CREATION_DATE)) "PO Date", 
d.segment1 "PO NUM",
d.type_lookup_code "PO Type", 
c.quantity_ordered "QTY ORDERED", 
c.quantity_cancelled "QTY CANCELLED", 
g.item_id "ITEM ID" , 
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE", 
(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount", 
(select 
decode(ph.approved_FLAG, 'Y', 'Approved') 
from po.po_headers_all ph 
where ph.po_header_ID = d.po_header_id)"PO Approved?", 
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT", 
to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", 
a.invoice_num "INVOICE NUMBER", 
(select 
decode(x.MATCH_STATUS_FLAG, 'A', 'Approved') 
from ap.ap_invoice_distributions_all x 
where x.INVOICE_DISTRIBUTION_ID = b.invoice_distribution_id)"Invoice Approved?", 
a.amount_paid,
h.amount, 
h.check_id, 
h.invoice_payment_id "Payment Id", 
i.check_number "Cheque Number", 
to_char(trunc(i.check_DATE)) "PAYMENT DATE" 
 
FROM AP.AP_INVOICES_ALL A, 
AP.AP_INVOICE_DISTRIBUTIONS_ALL B, 
PO.PO_DISTRIBUTIONS_ALL C, 
PO.PO_HEADERS_ALL D, 
PO.PO_VENDORS E, 
PO.PO_VENDOR_SITES_ALL F, 
PO.PO_LINES_ALL G, 
AP.AP_INVOICE_PAYMENTS_ALL H, 
AP.AP_CHECKS_ALL I 
where a.invoice_id = b.invoice_id 
and b.po_distribution_id = c. po_distribution_id (+) 
and c.po_header_id = d.po_header_id (+) 
and e.vendor_id (+) = d.VENDOR_ID 
and f.vendor_site_id (+) = d.vendor_site_id 
and d.po_header_id = g.po_header_id 
and c.po_line_id = g.po_line_id 
and a.invoice_id = h.invoice_id 
and h.check_id = i.check_id 
and f.vendor_site_id = i.vendor_site_id 
and c.PO_HEADER_ID is not null 
and a.payment_status_flag = 'Y' 
and d.type_lookup_code != 'BLANKET' 


-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2


----- List and all data entry from PR till PO

select distinct u.description "Requestor", 
porh.segment1 as "Req Number", 
trunc(porh.Creation_Date) "Created On", 
pord.LAST_UPDATED_BY, 
porh.Authorization_Status "Status", 
porh.Description "Description", 
poh.segment1 "PO Number", 
trunc(poh.Creation_date) "PO Creation Date", 
poh.AUTHORIZATION_STATUS "PO Status", 
trunc(poh.Approved_Date) "Approved Date"
from apps.po_headers_all poh, 
apps.po_distributions_all pod, 
apps.po_req_distributions_all pord, 
apps.po_requisition_lines_all porl, 
apps.po_requisition_headers_all porh, 
apps.fnd_user u 
where porh.requisition_header_id = porl.requisition_header_id 
and porl.requisition_line_id = pord.requisition_line_id 
and pord.distribution_id = pod.req_distribution_id(+) 
and pod.po_header_id = poh.po_header_id(+) 
and porh.created_by = u.user_id
order by 2 

-----list all Purchase Requisition without a Purchase Order that means  a PR has not been autocreated to PO.
  select 
  prh.segment1 "PR NUM", 
  trunc(prh.creation_date) "CREATED ON", 
  trunc(prl.creation_date) "Line Creation Date" ,
  prl.line_num "Seq #", 
  msi.segment1 "Item Num", 
  prl.item_description "Description", 
  prl.quantity "Qty", 
  trunc(prl.need_by_date) "Required By", 
  ppf1.full_name "REQUESTOR", 
  ppf2.agent_name "BUYER" 
  from 
  po.po_requisition_headers_all prh, 
  po.po_requisition_lines_all prl, 
  apps.per_people_f ppf1, 
  (select distinct agent_id,agent_name from apps.po_agents_v ) ppf2, 
  po.po_req_distributions_all prd, 
  inv.mtl_system_items_b msi, 
  po.po_line_locations_all pll, 
  po.po_lines_all pl, 
  po.po_headers_all ph 
  WHERE 
  prh.requisition_header_id = prl.requisition_header_id 
  and prl.requisition_line_id = prd.requisition_line_id 
  and ppf1.person_id = prh.preparer_id 
  and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date 
  and ppf2.agent_id(+) = msi.buyer_id 
  and msi.inventory_item_id = prl.item_id 
  and msi.organization_id = prl.destination_organization_id 
  and pll.line_location_id(+) = prl.line_location_id 
  and pll.po_header_id = ph.po_header_id(+) 
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+) 
  AND PRH.AUTHORIZATION_STATUS = 'APPROVED' 
  AND PLL.LINE_LOCATION_ID IS NULL 
  AND PRL.CLOSED_CODE IS NULL 
  AND NVL(PRL.CANCEL_FLAG,'N') <> 'Y'
  ORDER BY 1,2


---List PO Approval assignments
select pp.name
      ,pcf.control_function_name
      ,pcg.control_group_name
from   PO_POSITION_CONTROLS_ALL  ppc
      ,apps3_mtnaol.PER_POSITIONS pp
      ,PO_CONTROL_FUNCTIONS pcf
      ,PO_CONTROL_GROUPS_ALL pcg
where  ppc.org_id = 22970
and    ppc.position_id = pp.position_id
and    ppc.control_function_id = pcf.control_function_id
and    ppc.control_group_id = pcg.control_group_id
order by 1;